﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using HtmlAgilityPack;
using System.Net;
using System.IO;
using System.Web;

namespace LenderClearingHouse
{
    //This class builds data objects for the scrapes
    public static class ImportDataBuilders
    {
        //Creates the Nevada Legal News Datatable
        public static DataTable NevadaLegalNews()
        {
            //Create the datatable
            DataTable table = new DataTable("NevadaLegalNews");
            table.Columns.Add("ListingStatus", typeof(string));
            table.Columns.Add("AuctionDate", typeof(string));
            table.Columns.Add("SaleLocation", typeof(string));
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("APN", typeof(string));
            table.Columns.Add("ClientId", typeof(string));
            table.Columns.Add("EstimatedBid", typeof(string));
            table.Columns.Add("SoldPrice", typeof(string));
            table.Columns.Add("Trustee", typeof(string));
            table.Columns.Add("DateRecorded", typeof(string));
            table.Columns.Add("DocumentNumber", typeof(string));
            table.Columns.Add("PropertyLocation", typeof(string));
            table.Columns.Add("ZipCode", typeof(string));
            table.Columns.Add("BlankValue", typeof(string));

            return table;
        }

        //Creates the Nevada Legal News Rows and adds it to the passed table
        public static DataTable NevadaLegalNewsRow(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["ListingStatus"] = colvals[0];
                row["AuctionDate"] = colvals[1];
                row["SaleLocation"] = colvals[2];
                row["TsNumber"] = colvals[3];
                row["APN"] = colvals[4];
                row["ClientId"] = colvals[5];
                row["EstimatedBid"] = colvals[6];
                row["SoldPrice"] = colvals[7];
                row["Trustee"] = colvals[8];
                row["DateRecorded"] = colvals[9];
                row["DocumentNumber"] = colvals[10];
                row["PropertyLocation"] = colvals[11];
                row["BlankValue"] = colvals[12];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Creates the Parcell Appraisal DataTable
        public static DataTable ParcellAppraisal()
        {
            //Create the datatable
            DataTable table = new DataTable("ParcellAppraisal");
            table.Columns.Add("APN", typeof(string));
            table.Columns.Add("LotSize", typeof(string));
            table.Columns.Add("ConstructYear", typeof(string));
            table.Columns.Add("LandUse", typeof(string));
            table.Columns.Add("TotalSqft", typeof(string));
            table.Columns.Add("FirstFloorSqft", typeof(string));
            table.Columns.Add("Stories", typeof(string));
            table.Columns.Add("Pool", typeof(string));
            table.Columns.Add("SecondFloorSqft", typeof(string));
            table.Columns.Add("Bedrooms", typeof(string));
            table.Columns.Add("Spa", typeof(string));
            table.Columns.Add("Bathrooms", typeof(string));
            table.Columns.Add("GarageSqft", typeof(string));
            table.Columns.Add("AssessorDesc", typeof(string));
 
            return table;
        }

        //Creates the Parcell Appraisal Rows and adds it to the passed table
        public static DataTable ParcellAppraisalRow(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["APN"] = colvals[0];
                row["AssessorDesc"] = colvals[1];
                row["LotSize"] = colvals[2];
                row["ConstructYear"] = colvals[3];
                row["LandUse"] = colvals[4];
                row["TotalSqft"] = colvals[5];
                row["FirstFloorSqft"] = colvals[6];
                row["Stories"] = colvals[7];
                row["Pool"] = colvals[8];
                row["SecondFloorSqft"] = colvals[9];
                row["Bedrooms"] = colvals[10];
                row["Spa"] = colvals[11];
                row["Bathrooms"] = colvals[12];
                row["GarageSqft"] = colvals[13];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Creates the OpeningBids DataTable for LpsAsap 
        public static DataTable LpsAsap()
        {
            //Create the datatable
            DataTable table = new DataTable("OpeningBidsApn");
            table.Columns.Add("Apn", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));
            table.Columns.Add("SalesStatus", typeof(string));

            return table;
        }

        ////Creates the OpeningBids DataTable for LpsAsap adds it to the passed table
        public static DataTable LpsAsapRow(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                row["Apn"] = colvals[0].Replace(".", "");
                row["SalesStatus"] = colvals[1];
                row["OpeningBid"] = colvals[2];
                row["ScrapeSource"] = colvals[3];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Creates the OpeningBids DataTable for ReconTrust 
        public static DataTable ReconTrust()
        {
            //Create the datatable
            DataTable table = new DataTable("OpeningBids");
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));
            table.Columns.Add("DateOfSale", typeof(string));

            return table;
        }

        public static DataTable NdsCorp()
        {
            //Create the datatable
            DataTable table = new DataTable("OpeningBids");
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));
            table.Columns.Add("NdsCorp_SaleDate", typeof(string));

            return table;
        }

        ////Creates the OpeningBids DataTable for ReconTrust adds it to the passed table
        public static DataTable ReconTrustRow(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["TsNumber"] = colvals[0];
                row["OpeningBid"] = colvals[1];
                row["ScrapeSource"] = colvals[2];
                if (colvals.Length >= 4)
                {
                    row["DateOfSale"] = colvals[3];
                }

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        ////Creates the OpeningBids DataTable for ReconTrust adds it to the passed table
        public static DataTable ReconTrustRowOneZero(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["TsNumber"] = colvals[0];
                row["OpeningBid"] = colvals[1];
                row["ScrapeSource"] = colvals[2];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        ////Creates the OpeningBids DataTable for ReconTrust adds it to the passed table
        public static DataTable ReconTrustRowTwoZero(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["TsNumber"] = colvals[0];
                row["OpeningBid"] = colvals[1];
                row["ScrapeSource"] = colvals[2];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Creates the NdsCorp Datatable
        public static DataTable NdsCorpTable()
        {
            //Create the datatable
            DataTable table = new DataTable("OpeningBids");
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));

            return table;
        }//Create Datatable

        //Creates the NdsCorp Rows and adds it to the passed table
        public static DataTable NdsCorpRow(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["NdsCorp_SaleDate"] = colvals[0];
                row["TsNumber"] = colvals[1];
                row["OpeningBid"] = colvals[2];
                row["ScrapeSource"] = colvals[3];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Creates the NdsCorp Datatable
        public static DataTable RpsSaleTable()
        {
            //Create the datatable
            DataTable table = new DataTable("OpeningBids");
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));

            return table;
        }//Create Datatable

        //Creates the NdsCorp Rows and adds it to the passed table
        public static DataTable RpsSaleRow(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["TsNumber"] = colvals[0];
                row["OpeningBid"] = colvals[1];
                row["ScrapeSource"] = colvals[2];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Creates the OpeningBids DataTable for Priority Posting 
        public static DataTable PriorityPosting()
        {
            //Create the datatable
            DataTable table = new DataTable("OpeningBidsApn");
            table.Columns.Add("Apn", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));
            table.Columns.Add("PriorityPost_Status", typeof(string));

            return table;
        }

        ////Creates the OpeningBids DataTable for Priority Posting adds it to the passed table
        public static DataTable PriorityPostingRow(DataTable table, string[] colvals)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                //Build Row
                row["PriorityPost_Status"] = colvals[0];
                row["Apn"] = colvals[2];
                row["OpeningBid"] = colvals[1];
                row["ScrapeSource"] = colvals[3];

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Creates master table for csv import
        public static DataTable CreateMasterTableCSV()
        {
            //Create the datatable
            DataTable table = new DataTable("MasterTableCSV");
            table.Columns.Add("Trustee", typeof(string));
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("APN", typeof(string));
            table.Columns.Add("PropertyLocation", typeof(string));
            table.Columns.Add("ZipCode", typeof(string));
            table.Columns.Add("vo", typeof(string));
            table.Columns.Add("hoa", typeof(string));
            table.Columns.Add("AuctionDate", typeof(string));
            table.Columns.Add("ListingStatus", typeof(string));
            table.Columns.Add("EstimatedBid", typeof(string));
            table.Columns.Add("MaxBid", typeof(string));
            table.Columns.Add("EstVal", typeof(string));
            table.Columns.Add("Tax", typeof(string));
            table.Columns.Add("Nos", typeof(string));
            table.Columns.Add("Liens", typeof(string));
            table.Columns.Add("OnOff", typeof(string));
            table.Columns.Add("PropertyDetails", typeof(string));
            
          table.Columns.Add("Source", typeof(string));
          table.Columns.Add("DateRecorded", typeof(string));
          table.Columns.Add("EscrowCompany", typeof(string));
          table.Columns.Add("PrelistStatus", typeof(string));
          table.Columns.Add("PurchasePrice", typeof(string));
          table.Columns.Add("RehabCosts", typeof(string));
          table.Columns.Add("MiscNotes", typeof(string));

            return table;
        }

        //Creates the Parcell Appraisal DataTable
        public static DataTable CreateParcellTableCSV()
        {
            //Create the datatable
            DataTable table = new DataTable("ParcellAppraisal");
            table.Columns.Add("APN", typeof(string));
            table.Columns.Add("Stories", typeof(string));
            table.Columns.Add("ConstructYear", typeof(string));
            table.Columns.Add("TotalSqft", typeof(string));
            table.Columns.Add("LotSize", typeof(string));
            table.Columns.Add("Bedrooms", typeof(string));
            table.Columns.Add("Bathrooms", typeof(string));
            table.Columns.Add("GarageSqft", typeof(string));
            table.Columns.Add("Pool", typeof(string));
            table.Columns.Add("AssessorDesc", typeof(string));

            return table;
        }

        //Creates apn table for csv import
        public static DataTable CreateApnCSV()
        {
            //Create the datatable
            DataTable table = new DataTable("ApnCSV");
            table.Columns.Add("Apn", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));

            return table;
        }

        //Creates tsNumber table for csv import
        public static DataTable CreateTsBidCSV()
        {
            //Create the datatable
            DataTable table = new DataTable("TsBidCSV");
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("ScrapeSource", typeof(string));

            return table;
        }

        //Create the WorkingList table for csv import
        public static DataTable CreateWorkingListTableCSV()
        {
            //Create the datatable
            DataTable table = new DataTable("WorkingListCSV");
            table.Columns.Add("PropertyId", typeof(int));
            table.Columns.Add("Trustee", typeof(string));
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("APN", typeof(string));
            table.Columns.Add("PropertyLocation", typeof(string));
            table.Columns.Add("ZipCode", typeof(string));
            table.Columns.Add("vo", typeof(string));
            table.Columns.Add("hoa", typeof(string));
            table.Columns.Add("AuctionDate", typeof(string));
            table.Columns.Add("ListingStatus", typeof(string));
            table.Columns.Add("EstimatedBid", typeof(string));
            table.Columns.Add("MaxBid", typeof(string));
            table.Columns.Add("EstVal", typeof(string));
            table.Columns.Add("Tax", typeof(string));
            table.Columns.Add("Nos", typeof(string));
            table.Columns.Add("Liens", typeof(string));
            table.Columns.Add("OnOff", typeof(string));
            table.Columns.Add("PropertyDetails", typeof(string));
            table.Columns.Add("Stories", typeof(string));
            table.Columns.Add("ConstructYear", typeof(string));
            table.Columns.Add("TotalSqft", typeof(string));
            table.Columns.Add("LotSize", typeof(string));
            table.Columns.Add("Bedrooms", typeof(string));
            table.Columns.Add("Bathrooms", typeof(string));
            table.Columns.Add("GarageSqft", typeof(string));
            table.Columns.Add("Pool", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("AssessorDesc", typeof(string));

            table.Columns.Add("Source", typeof(string));
            table.Columns.Add("DateRecorded", typeof(string));
            table.Columns.Add("EscrowCompany", typeof(string));
            table.Columns.Add("PrelistStatus", typeof(string));
            table.Columns.Add("PurchasePrice", typeof(string));
            table.Columns.Add("RehabCosts", typeof(string));
            table.Columns.Add("MiscNotes", typeof(string));

            return table;
        }

        //Create the WorkingList table for csv import
        public static DataTable CreateSdReoMasterListTableCSV()
        {
            //Create the datatable
            DataTable table = new DataTable("SDREOListCSV");
            table.Columns.Add("PropertyId", typeof(int));
            table.Columns.Add("Trustee", typeof(string));
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("APN", typeof(string));
            table.Columns.Add("PropertyLocation", typeof(string));
            table.Columns.Add("ZipCode", typeof(string));
            table.Columns.Add("vo", typeof(string));
            table.Columns.Add("hoa", typeof(string));
            table.Columns.Add("AuctionDate", typeof(string));
            table.Columns.Add("ListingStatus", typeof(string));
            table.Columns.Add("EstimatedBid", typeof(string));
            table.Columns.Add("MaxBid", typeof(string));
            table.Columns.Add("EstVal", typeof(string));
            table.Columns.Add("Tax", typeof(string));
            table.Columns.Add("Nos", typeof(string));
            table.Columns.Add("Liens", typeof(string));
            table.Columns.Add("OnOff", typeof(string));
            table.Columns.Add("PropertyDetails", typeof(string));
            table.Columns.Add("Stories", typeof(string));
            table.Columns.Add("ConstructYear", typeof(string));
            table.Columns.Add("TotalSqft", typeof(string));
            table.Columns.Add("LotSize", typeof(string));
            table.Columns.Add("Bedrooms", typeof(string));
            table.Columns.Add("Bathrooms", typeof(string));
            table.Columns.Add("GarageSqft", typeof(string));
            table.Columns.Add("Pool", typeof(string));
            table.Columns.Add("OpeningBid", typeof(string));
            table.Columns.Add("AssessorDesc", typeof(string));

            table.Columns.Add("Source", typeof(string));
            table.Columns.Add("DateRecorded", typeof(string));
            table.Columns.Add("EscrowCompany", typeof(string));
            table.Columns.Add("PrelistStatus", typeof(string));
            table.Columns.Add("PurchasePrice", typeof(string));
            table.Columns.Add("RehabCosts", typeof(string));
            table.Columns.Add("MiscNotes", typeof(string));

            return table;
        }

        //Creates master table for csv import
        public static DataTable CreateLvReoMasterTableCSV()
        {
            //Create the datatable
            DataTable table = new DataTable("MasterTableCSV");
            table.Columns.Add("Trustee", typeof(string));
            table.Columns.Add("TsNumber", typeof(string));
            table.Columns.Add("APN", typeof(string));
            table.Columns.Add("PropertyLocation", typeof(string));
            table.Columns.Add("ZipCode", typeof(string));
            table.Columns.Add("vo", typeof(string));
            table.Columns.Add("hoa", typeof(string));
            table.Columns.Add("AuctionDate", typeof(string));
            table.Columns.Add("ListingStatus", typeof(string));
            table.Columns.Add("EstimatedBid", typeof(string));
            table.Columns.Add("MaxBid", typeof(string));
            table.Columns.Add("EstVal", typeof(string));
            table.Columns.Add("Tax", typeof(string));
            table.Columns.Add("Nos", typeof(string));
            table.Columns.Add("Liens", typeof(string));
            table.Columns.Add("OnOff", typeof(string));
            table.Columns.Add("PropertyDetails", typeof(string));
            table.Columns.Add("ApnOpeningBid", typeof(string));
            table.Columns.Add("TsOpeningBid", typeof(string));

            table.Columns.Add("Source", typeof(string));
            table.Columns.Add("DateRecorded", typeof(string));
            table.Columns.Add("EscrowCompany", typeof(string));
            table.Columns.Add("PrelistStatus", typeof(string));
            table.Columns.Add("PurchasePrice", typeof(string));
            table.Columns.Add("RehabCosts", typeof(string));
            table.Columns.Add("MiscNotes", typeof(string));

            return table;
        }

        internal static void LpsAsapRow(DataTable table, string apn, string saleStatus, string amt)
        {
            DataRow row;
            row = table.NewRow(); //Create new row
            try
            {
                row["Apn"] = apn.Replace(".", "");
                row["SalesStatus"] = saleStatus;
                row["OpeningBid"] = amt;
                row["ScrapeSource"] = "LPS";

                table.Rows.Add(row); //Add row to table
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }//End Class
}//End namespace
